/*
 * SisAP - Sistema de Alocacao de Professores
 *
 * Copyright (C) 2011  Macambira Team.
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 */
package com.googlecode.persistence;

import com.googlecode.conection.ConectaDB;
import com.googlecode.entity.Professor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 * Classe responsavel por fazer a persistencia com o banco de dados do professor
 *
 * @version 0.1 03 dec 2011
 * @author Jafet
 */
public class ProfessorDAO {
    private static ProfessorDAO professorDAO;
    
    public static ProfessorDAO getInstance() {
        synchronized (ProfessorDAO.class){
            if (professorDAO == null){
                professorDAO = new ProfessorDAO();
            }
        }
        return professorDAO;
    }
    
    public void CadastrarProfessor(Professor professor) throws ClassNotFoundException, SQLException {
        PreparedStatement ps;
        try {
            Connection con = (Connection) ConectaDB.getInstance().getCon();
            ps = (PreparedStatement) con.prepareStatement("INSERT INTO professores"
                    + "(idProf, nome, email, tel, tel_opc)"
                    + "VALUES (?,?,?,?,?)");
            ps.setString(1, professor.getId());
            ps.setString(2, professor.getNome());
            ps.setString(3, professor.getEmail());
            ps.setString(4, professor.getTelefone());
            ps.setString(5, professor.getTelefoneOpcional());
            ps.execute();
            con.close();
            JOptionPane.showMessageDialog(null, "Cadastro realizado com Sucesso");
        }catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Professor não pode ser cadastrado");
            throw new RuntimeException(e);
        }
    }
    public void RemoverProfessorId(String idProf) throws ClassNotFoundException, SQLException {
        PreparedStatement ps;
        try {
        Connection con = (Connection) ConectaDB.getInstance().getCon();
        ps = (PreparedStatement) con.prepareStatement("DELETE FROM professores WHERE ='" + idProf + "'");
        ps.execute();
        con.close();
        JOptionPane.showMessageDialog(null, "Removido com sucesso");
      } catch(SQLException e) {
          JOptionPane.showMessageDialog(null, "Professor não pode ser removido");
          throw new RuntimeException(e);
      }
    }
    
    public void RemoverProfessorNome(String nomeProf) throws ClassNotFoundException, SQLException {
        PreparedStatement ps;
        try {
        Connection con = (Connection) ConectaDB.getInstance().getCon();
        ps = (PreparedStatement) con.prepareStatement("DELETE FROM professores WHERE ='" + nomeProf + "'");
        ps.execute();
        con.close();
        JOptionPane.showMessageDialog(null, "Removido com sucesso");
      } catch(SQLException e) {
          JOptionPane.showMessageDialog(null, "Professor não pode ser removido");
          throw new RuntimeException(e);
      }
    }
    
    public void EditarProfessor(Professor professor) throws ClassNotFoundException, SQLException {
        PreparedStatement ps;
        try {
            Connection con = (Connection) ConectaDB.getInstance().getCon();
            ps = (PreparedStatement) con.prepareStatement("UPDATE professores nome = ?, "
                    + "email = ?, tel = ?, tel_opc = ? WHERE idProf = ?");
            ps.setString(1, professor.getNome());
            ps.setString(2, professor.getEmail());
            ps.setString(3, professor.getTelefone());
            ps.setString(4, professor.getTelefoneOpcional());
            ps.setString(5, professor.getId());
            ps.execute();
            con.close();
            JOptionPane.showMessageDialog(null, "Alteração Realizada");
        } catch(SQLException e) {
            JOptionPane.showMessageDialog(null, "Alteração não foi realizada");
            throw new RuntimeException(e);
        }
    }
    public Professor ConsultaProfessorId(String idProf) throws ClassNotFoundException, SQLException {
        ResultSet rs;
        PreparedStatement ps;
        Professor p = new Professor();
        try {
            Connection con = (Connection) ConectaDB.getInstance().getCon();
            ps = (PreparedStatement) con.prepareStatement("SELECT * FROM professores WHERE idProf = ?");
            ps.setString(1, idProf);
            rs = ps.executeQuery();
            while(rs.next()){
                p.setId(rs.getString("idProf"));
                p.setNome(rs.getString("nome"));
                p.setEmail(rs.getString("email"));
                p.setTelefone(rs.getString("tel"));
                p.setTelefoneOpcional(rs.getString("tel_opc"));
            }
            con.close();
        } catch(SQLException e) {
            JOptionPane.showMessageDialog(null, "Não pode ser encontrado");
            throw new RuntimeException(e);
        }
        return p;
    }
    public Professor ConsultaProfessorNome(String nomeProf) throws ClassNotFoundException, SQLException {
        ResultSet rs;
        PreparedStatement ps;
        Professor p = new Professor();
        try {
            Connection con = (Connection) ConectaDB.getInstance().getCon();
            ps = con.prepareStatement("SELECT * FROM professores WHERE nome = ?");
            ps.setString(1, nomeProf);
            rs = ps.executeQuery();
            
            while (rs.next()){
                p.setId(rs.getString("idProf"));
                p.setNome(rs.getString("nome"));
                p.setEmail(rs.getString("email"));
                p.setTelefone(rs.getString("tel"));
                p.setTelefoneOpcional(rs.getString("tel_opc"));
            }
        ps.close();           
        } catch(SQLException e) {
            JOptionPane.showMessageDialog(null, "Não pode ser encontrado");
            throw new RuntimeException(e);
        }
        return p;
    }
    public List<Professor> ConsultaTodosProfessores() {
        List<Professor> professores = new LinkedList<Professor>();
        ResultSet rs;
        PreparedStatement ps;
        Professor p = new Professor();
        try {
            Connection con = (Connection) ConectaDB.getInstance().getCon();
            ps = (PreparedStatement) con.prepareStatement("SELECT * FROM professores");
            //ps.setString(1, idProf);
            rs = ps.executeQuery();
            while(rs.next()){
                p.setId(rs.getString("idProf"));
                p.setNome(rs.getString("nome"));
                p.setEmail(rs.getString("email"));
                p.setTelefone(rs.getString("tel"));
                p.setTelefoneOpcional(rs.getString("tel_opc"));
                professores.add(p);
            }
            con.close();
            return professores;
        } catch(Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Não pode ser encontrado");
        }
        return null;
    }

}
